package com.shop.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import com.shop.bean.Good;
import com.shop.dao.BaseDao;
import com.shop.dao.GoodsDao;

public class GoodsDaoImpl implements GoodsDao {

	@Override
	public List<Good> selectGoods() {
		List<Good> goodList=new ArrayList();
		Connection con=BaseDao.getCon();
		ResultSet res=null;
		PreparedStatement ps=null;
		try {
			String sql="select * from goods";
			ps=con.prepareStatement(sql);
			res=ps.executeQuery();
			while(res.next()) {
				Good goods=new Good();
				goods.setGoodsId(res.getInt("goodsId"));
				goods.setGoodsName(res.getString("goodsName"));
				goods.setGoodsNum(res.getInt("goodsNum"));
				goods.setGoodsFactory(res.getString("goodsFactory"));
				goods.setGoodsPrice(res.getDouble("goodsPrice"));
				goods.setGoodsType(res.getString("goodsType"));
				goodList.add(goods);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			BaseDao.closeAll(res, ps, con);
		}
		return goodList;
	}

	@Override
	public List<Good> selectAllGoods(int pageNum, int pageSize) {
		List<Good> goodList=new ArrayList();
		Connection con=BaseDao.getCon();
		ResultSet res=null;
		PreparedStatement ps=null;
		try {
			String sql="select * from goods limit ?,?";
			ps=con.prepareStatement(sql);
			ps.setInt(1, (pageNum-1)*pageSize);
			ps.setInt(2, pageSize);
			res=ps.executeQuery();
			while(res.next()) {
				Good goods=new Good();
				goods.setGoodsId(res.getInt("goodsId"));
				goods.setGoodsName(res.getString("goodsName"));
				goods.setGoodsNum(res.getInt("goodsNum"));
				goods.setGoodsFactory(res.getString("goodsFactory"));
				goods.setGoodsPrice(res.getDouble("goodsPrice"));
				goods.setGoodsType(res.getString("goodsType"));
				goodList.add(goods);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			BaseDao.closeAll(res, ps, con);
		}
		return goodList;
	}

	@Override
	public int selectTotalNum() {
		int rows = 0;
		Connection con = null;
		PreparedStatement ps = null;
		ResultSet res = null;
		String sql = "SELECT count(1) as total FROM goods";
		try {
			con = BaseDao.getCon();
			ps = con.prepareStatement(sql);
			res = ps.executeQuery();
			if(res.next()) {
				rows = res.getInt("total");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			BaseDao.closeAll(res, ps, con);
		}
		return rows;

	}

	@Override
	public int deleteGoodsById(int goodsId) {
		Connection con = null;
		PreparedStatement ps = null;
		int result = 0;
		String sql = "DELETE FROM goods WHERE goodsId = ?";
		try {
			con = BaseDao.getCon();
			ps = con.prepareStatement(sql);
			ps.setInt(1,goodsId);
			result = ps.executeUpdate();
		} catch (SQLException e) {			
			e.printStackTrace();
		} finally {
			BaseDao.closeAll(null, ps, con);
		}
		return result;
	}
}
